<?php
/**
 * (model) 备货sku备货（JP站点）
 */
namespace App\Models\SsBasic;

use App\Models\Basics\SysCountry;
use App\Models\Basics\SysPlat;
use App\Models\Inventory\PublicCn;
use App\Models\Product\ProductClassify;
use App\Models\Product\ProductSku;
use App\Util\RExcel;
use App\Util\User;
use Illuminate\Database\Eloquent\Model;
class JpStockingSku extends Model
{
    protected $table = 's_jp_stocking_sku';

    protected $guarded = [];

    public static $_status = [
        1 => '未申请',
        2 => '待审批',
        3 => '驳回',
        4 => '通过',
    ];

    public function area_sku(){
        return $this->belongsTo(JpSku::class,'area_psku','area_psku');
    }

    public function ratio(){
        return $this->hasOne(JpStockingSkuRatio::class,'area_psku','area_psku');
    }

    public function country(){
        return $this->belongsTo(SysCountry::class,'country_id');
    }

    public function sku(){
        return $this->belongsTo(ProductSku::class,'psku_id');
    }

    public function plat(){
        return $this->belongsTo(SysPlat::class,'plat_id','plat_id');
    }

    public function skuClassify(){
        return $this->hasOneThrough(
            ProductClassify::class,
            ProductSku::class,
            'id',//中间表
            'id',//目标表
            'psku_id',//当前模型
            'classify_id'//中间表
        );
    }

    /**
     * 最快可售天数 => 最快可售日期
     * @param $saleDays
     * @return false|string
     */
    public static function getSaleDate($saleDays){
        return date('Y-m-d',strtotime('+'.$saleDays.' days'));
    }

    /**
     * FBA库存
     * @param $model
     * @return mixed
     */
    private static function _getFBAInventory($model){
        return $model->fba_instant_num + $model->fba_transit_num;
    }

    /**
     * FBA库存 + 海外仓库存
     * @param $model
     * @return mixed
     */
    private static function _getFBAOSInventory($model){
        return $model->fba_instant_num + $model->fba_transit_num +
            $model->os_instant_num + $model->os_transit_num;
    }

    /**
     * 所有库存(不包含公共库存)
     * @param $model
     * @return mixed
     */
    private static function _getAllInventory($model){
        return $model->fba_instant_num + $model->fba_transit_num +
            $model->cn_instant_num + $model->cn_transit_num +
            $model->os_instant_num + $model->os_transit_num;
    }

    /**
     * 获取当前发货库存系数
     * @param $model
     * @return float|int|string
     */
    public static function getShipCurrentFactor($model){
        $inventory = self::_getFBAInventory($model);
        $estimate = JpSkuEstimate::getEstimate($model);
        return JpSkuEstimate::getInventoryFactor($inventory,$estimate);
    }

    /**
     * 获取当前发货库存系数 + OS库存
     * @param $model
     * @return float|int|string
     */
    public static function getShipWithOSCurrentFactor($model){
        $inventory = self::_getFBAOSInventory($model);
        $estimate = JpSkuEstimate::getEstimate($model);
        return JpSkuEstimate::getInventoryFactor($inventory,$estimate);
    }

    /**
     * 获取当前备货库存系数
     * @param $model
     * @return float|int|string
     */
    public static function getStockCurrentFactor($model){
        $inventory = self::_getAllInventory($model);
        $estimate = JpSkuEstimate::getEstimate($model);
        return JpSkuEstimate::getInventoryFactor($inventory,$estimate);
    }

    /**
     * 到最快可售日期时的总需求量
     * @param $model
     * @param $saleDays
     * @return float|int
     */
    private static function _getAvailableEstimateNum($model,$saleDays){
        $initDay = date('j');//当前月第N天
        $j = 0;//到可售周期预估月
        $count = 0;
        $thisDays = 0;//预估月的天数
        $daysTotal = 0;//到最快可售日期所在预估月的总天数
        for($i = 0; $i < JpSkuEstimate::ESTIMATE_NUM; $i++){
            $thisDays = date('t',strtotime("midnight first day of +{$i} month"));//当月共多少天
            $daysTotal += $thisDays;
            $day = $daysTotal - $initDay;
            if($day >= $saleDays){
                $j = $i;
                break;
            }
            $count++;
        }
        $initDays = date('t');//当前月总天数
        $sum0 = floor($initDay / $initDays * $model->estimate_num0);
        if($count == JpSkuEstimate::ESTIMATE_NUM){//可售周期过长，需求量 = 总预估量-过期的预估量
            $sum = 0;
            for ($k = 0; $k < $count; $k++){
                $sum += $model->{'estimate_num'.$k};
            }
            $sum -= $sum0;
        }else{//可售周期在预估内
            $kDay = date('j',strtotime("+{$saleDays} days"));//到最快可售天数所在月的当月第N天
            if($j == 0){ //可售周期在当前月，需求量=到最快可售日期的预估量-过期的预估量
                $sum = floor($kDay / $thisDays * $model->estimate_num0) - $sum0;
            }else{//可售周期不在当前月，需求量=到可售周期预估月（$j）-过期的预估量-（$j）多余的预估量
                $sum1 = 0;
                for ($l = 0; $l < $j; $l++){
                    $sum1 += $model->{'estimate_num'.$l};
                }
                $sum2 = floor($kDay / $thisDays * $model->{'estimate_num'.$j});
                $sum = $sum1 + $sum2 - $sum0;
            }
        }
        return $sum;
    }

    /**
     * 获取海运最快可售日期节点往后需求量+往后延两个月的需求量
     * @param $model
     * @return float|int
     */
    private static function _getSeaAvailableLast2MonthNum($model){
        $saleDays = $model->sea_available;
        $initDay = date('j');//当前月第N天
        $j = 0;//到可售周期预估月
        $count = 0;
        $thisDays = 0;//预估月的天数
        $daysTotal = 0;//到最快可售日期所在预估月的总天数
        for($i = 0; $i < JpSkuEstimate::ESTIMATE_NUM; $i++){
            $thisDays = date('t',strtotime("midnight first day of +{$i} month"));//当月共多少天
            $daysTotal += $thisDays;
            $day = $daysTotal - $initDay;
            if($day >= $saleDays){
                $j = $i;
                break;
            }
            $count++;
        }
        if($count == JpSkuEstimate::ESTIMATE_NUM) {//可售周期过长，需求量不足，取0
            $sum = 0;
        }else{
            $kDay = date('j',strtotime("+{$saleDays} days"));//到最快可售天数所在月的当月第N天
            if($j == 0){ //可售周期在当前月
                $sum1 = floor(($thisDays - $kDay) / $thisDays * $model->estimate_num0);
                $sum2 = $model->estimate_num1 + $model->estimate_num2;
                $sum = $sum1 + $sum2;
            }else{//可售周期不在当前月
                $sum1 = floor(($thisDays - $kDay) / $thisDays * $model->{'estimate_num'.$j});
                $_count = 0;
                $sum2 = 0;
                for ($k = $j + 1; $k < JpSkuEstimate::ESTIMATE_NUM; $k++){
                    $_count++;
                    $sum2 += $model->{'estimate_num'.$k};
                    if($_count == 2)break;
                }
                $sum = $sum1 + $sum2;
            }
        }
        return $sum;
    }

    /**
     * 库存可售天数
     * 若库存大于需求总量，则剩余库存全部按照最后一个月的预估量计算
     * @param $model
     * @param $inventory
     * @return false|float|int|string
     */
    private static function _getInventoryAvailableDays($model,$inventory){
        $initDay = date('j');//当前月第N天
        $initDays = date('t');//当前月共多少天
        $sum0 = floor(($initDays - $initDay) / $initDays * $model->estimate_num0);
        if($sum0 >= $inventory){
            $days = $model->estimate_num0 ? floor($inventory * $initDays / $model->estimate_num0) : 0;
        }else{
            $days0 = $initDays - $initDay;
            $inventory -= $sum0;
            $days1 = 0;
            for($i = 1; $i < JpSkuEstimate::ESTIMATE_NUM; $i++){
                $thisDays = date('t',strtotime("midnight first day of +{$i} month"));//当月共多少天
                if($inventory >= $model->{'estimate_num'.$i}){
                    $days1 += $thisDays;
                    $inventory -= $model->{'estimate_num'.$i};
                }else{
                    $days1 += ($model->{'estimate_num'.$i}) ? floor($inventory * $thisDays / $model->{'estimate_num'.$i}) : 0;
                    break;
                }
            }
            if($i == JpSkuEstimate::ESTIMATE_NUM){
                $j = $i - 1;
                $thisDays = date('t',strtotime("midnight first day of +{$j} month"));//当月共多少天
                $days1 += ($model->{'estimate_num'.$j}) ? floor($inventory * $thisDays / $model->{'estimate_num'.$j}) : 0;
            }
            $days = $days0 + $days1;
        }
        return $days;
    }

    /**
     * 备货推荐和备货说明
     * @param $model
     * @return float|int|mixed
     */
    public static function getSystemTJ($model){
        if(self::setMonthLe2Month($model->set_date,$model->sea_available)){//指定月 <= 海运+2月
            return self::_getSystemTJBy2Month($model);
        }else{
            return self::_getSystemTJBySetMonth($model);
        }
    }

    /**
     * 备货推荐和备货说明 指定月 <= 海运+2月
     * 总需求为：到海运+2月
     * @param $model
     * @return array
     */
    private static function _getSystemTJBy2Month($model){
        //到空运最快可售日期时的总需求量
        $airNum = self::_getAvailableEstimateNum($model,$model->air_available);
        //到海运最快可售日期时的总需求量
        $seaNum = self::_getAvailableEstimateNum($model,$model->sea_available);
        //库存总量
        $inventory = self::_getAllInventory($model);
        //到海运最快可售日期节点月往后再延2个月的总需求量
        $last2MonthNum = self::_getSeaAvailableLast2MonthNum($model);
        //库存可售天数
        $inventoryAvailable = self::_getInventoryAvailableDays($model,$inventory);
        $desc = [];
        if($inventory >= $seaNum){//库存总量大于等于海运时，推荐数量=海运需求量+海运往后延两个月的需求量-总库存
            $num = $seaNum + $last2MonthNum - $inventory; //推荐备货数量
            $desc['海运'] = '('.$num.')';//推荐备货数量说明 全部海运备货
        }else if($inventory >= $airNum && $inventory < $seaNum){//库存总量在空运和海运之间时，推荐数量=海运需求量+海运往后延两个月的需求量-总库存
            $num = $seaNum + $last2MonthNum - $inventory; //推荐备货数量
            $inventoryAvailableDate = self::getSaleDate($inventoryAvailable);
            $seaAvailableDate = self::getSaleDate($model->sea_available);
            //推荐备货数量说明 库存可售日期~海运可售日期为空运备货，之后为海运备货
            $desc['空运'] = $inventoryAvailableDate.'~'.$seaAvailableDate.'('.($seaNum-$inventory).')';
            $desc['海运'] = $seaAvailableDate.'~'.'('.$last2MonthNum.')';
        }else{//库存总量小于空运时，推荐数量=海运需求量+海运往后延两个月的需求量-空运需求量
            $num = $seaNum + $last2MonthNum - $airNum; //推荐备货数量
            //推荐备货数量说明 库存可售日期~空运可售日期为断货，空运可售日期到海运可售日期为空运备货，之后为海运备货
            $inventoryAvailableDate = self::getSaleDate($inventoryAvailable);
            $airAvailableDate = self::getSaleDate($model->air_available);
            $seaAvailableDate = self::getSaleDate($model->sea_available);
            $desc['断货'] = $inventoryAvailableDate.'~'.$airAvailableDate;
            $desc['空运'] = $airAvailableDate.'~'.$seaAvailableDate.'('.($seaNum-$airNum).')';
            $desc['海运'] = $seaAvailableDate.'~'.'('.$last2MonthNum.')';
        }
        return ['num' => $num,'desc' => $desc,'free' => 0];
    }

    /**
     * 备货推荐和备货说明 指定月 > 海运+2月
     * 总需求为：到指定月
     * @param $model
     * @return array
     */
    private static function _getSystemTJBySetMonth($model){
        //到空运最快可售日期时的总需求量
        $airNum = self::_getAvailableEstimateNum($model,$model->air_available);
        //到海运最快可售日期时的总需求量
        $seaNum = self::_getAvailableEstimateNum($model,$model->sea_available);
        //库存总量
        $inventory = self::_getAllInventory($model);
        //到海运最快可售日期节点月往后再延2个月的总需求量
        $last2MonthNum = self::_getSeaAvailableLast2MonthNum($model);
        //到海运最快可售日期节点月往后再延至指定月总需求量
        $setMonthNum = self::getSeaAvailableSetMonthNum($model);
        //库存可售天数
        $inventoryAvailable = self::_getInventoryAvailableDays($model,$inventory);
        $desc = [];
        if($inventory >= $seaNum){//库存总量大于等于海运时，推荐数量=海运需求量+海运往后延至指定月的需求量-总库存
            $num = $seaNum + $setMonthNum - $inventory; //推荐备货数量
            $desc['海运'] = '('.$num.')';//推荐备货数量说明 全部海运备货
        }else if($inventory >= $airNum && $inventory < $seaNum){//库存总量在空运和海运之间时，推荐数量=海运需求量+海运往后延至指定月的需求量-总库存
            $num = $seaNum + $setMonthNum - $inventory; //推荐备货数量
            $inventoryAvailableDate = self::getSaleDate($inventoryAvailable);
            $seaAvailableDate = self::getSaleDate($model->sea_available);
            //推荐备货数量说明 库存可售日期~海运可售日期为空运备货，之后为海运备货
            $desc['空运'] = $inventoryAvailableDate.'~'.$seaAvailableDate.'('.($seaNum-$inventory).')';
            $desc['海运'] = $seaAvailableDate.'~'.'('.$setMonthNum.')';
        }else{//库存总量小于空运时，推荐数量=海运需求量+海运往后延至指定月的需求量-空运需求量
            $num = $seaNum + $setMonthNum - $airNum; //推荐备货数量
            //推荐备货数量说明 库存可售日期~空运可售日期为断货，空运可售日期到海运可售日期为空运备货，之后为海运备货
            $inventoryAvailableDate = self::getSaleDate($inventoryAvailable);
            $airAvailableDate = self::getSaleDate($model->air_available);
            $seaAvailableDate = self::getSaleDate($model->sea_available);
            $desc['断货'] = $inventoryAvailableDate.'~'.$airAvailableDate;
            $desc['空运'] = $airAvailableDate.'~'.$seaAvailableDate.'('.($seaNum-$airNum).')';
            $desc['海运'] = $seaAvailableDate.'~'.'('.$setMonthNum.')';
        }
        $free = $inventory - ($seaNum + $last2MonthNum); //可调节库存
        return ['num' => $num,'desc' => $desc,'free' => $free < 0 ? 0 : $free];
    }

    /**
     * 指定月 > 海运+2个月
     * 海运最快可售节点往后推至指定月
     * @param $model
     * @return float|int
     */
    private static function getSeaAvailableSetMonthNum($model){
        $date = date('Y-m-t',strtotime($model->set_date));//当月共多少天
        $setDays = getDayDiffByDateStr(date('Y-m-d'),$date);
        $saleDays = $model->sea_available;
        if($setDays <= $saleDays){//若指定月在海运最快可售节点前，返回0
            return 0;
        }
        $initDay = date('j');//当前月第N天
        $initDays = date('t');//当前月共多少天
        $estimateDaysTotal = $initDays - $initDay;//初始月剩余天数,预估天数
        for($i = 1; $i < JpSkuEstimate::ESTIMATE_NUM; $i++){
            $estimateDaysTotal += date('t',strtotime("midnight first day of +{$i} month"));//当月共多少天
        }
        if($estimateDaysTotal <= $saleDays){//若max预估月在海运最快可售节点前，返回0
            return 0;
        }
        if($estimateDaysTotal <= $setDays){//若预估月在指定节点前，返回将指定月设为为max预估月
            $setDays = $estimateDaysTotal;
        }
        $setNum = self::_getAvailableEstimateNum($model,$setDays);
        $seaNum = self::_getAvailableEstimateNum($model,$saleDays);
        return $setNum > $seaNum ? ($setNum - $seaNum) : 0;
    }

    /**
     * 积压货值
     * @param $num
     * @param $unitPrice
     * @return float
     */
    public static function getOverStockValue($num,$unitPrice){
        return round($num * $unitPrice,3);
    }

    /**
     * 获取海运+两个月
     * @param $seaSaleDays
     * @return false|string
     */
    public static function get2Date($seaSaleDays){
        $date = self::getSaleDate($seaSaleDays);
        return date('Y-m-01',strtotime("midnight first day of +2 month",strtotime($date)));
    }

    /**
     * 是否 指定月 <= 海运+2月
     * @param $setDate
     * @param $seaSaleDays
     * @return bool
     */
    private static function setMonthLe2Month($setDate,$seaSaleDays){
        if(!$setDate)return true;
        $setTime = strtotime($setDate);
        $date = self::get2Date($seaSaleDays);
        $seaTime = strtotime($date);
        return $setTime <= $seaTime ? true : false;
    }

    /**
     * 积压库存
     * @param $model
     * @return float|int|mixed
     */
    public static function getOverStock($model){
        if(self::setMonthLe2Month($model->set_date,$model->sea_available)){//指定月 <= 海运+2月
            return self::_getOverStockBy2Month($model);
        }else{
            return self::_getOverStockBySetMonth($model);
        }
    }

    /**
     * 指定月 <= 海运+2月
     * 积压库存 总库存 - （海运+2月）
     * @param $model
     * @return float|int|mixed
     */
    private static function _getOverStockBy2Month($model){
        //到海运最快可售日期时的总需求量
        $seaNum = self::_getAvailableEstimateNum($model,$model->sea_available);
        //库存总量
        $inventory = self::_getAllInventory($model);
        //到海运最快可售日期节点月往后再延2个月的总需求量
        $last2MonthNum = self::_getSeaAvailableLast2MonthNum($model);
        $estimateNum = $seaNum + $last2MonthNum;
        return $inventory > $estimateNum ? ($inventory - $estimateNum) : 0;
    }

    /**
     * 指定月 > 海运+2月
     * 积压库存 总库存 - 指定月总需求
     * @param $model
     * @return float|int|mixed
     */
    private static function _getOverStockBySetMonth($model){
        //到海运最快可售日期时的总需求量
        $seaNum = self::_getAvailableEstimateNum($model,$model->sea_available);
        //库存总量
        $inventory = self::_getAllInventory($model);
        //到海运最快可售日期节点月往后再延至指定月总需求量
        $setMonthNum = self::getSeaAvailableSetMonthNum($model);
        $estimateNum = $seaNum + $setMonthNum;
        return $inventory > $estimateNum ? ($inventory - $estimateNum) : 0;
    }


    /**
     * 判断计算推荐所对应的是指定月还是海运+2月
     * @param $setDate
     * @param $seaSaleDays
     * @return float|int
     */
    public static function setMonthOr2Month($setDate,$seaSaleDays){
        if(self::setMonthLe2Month($setDate,$seaSaleDays)) {//指定月 <= 海运+2月  == 海运+2月
            $date = self::get2Date($seaSaleDays);
        }else{
            $date = $setDate;
        }
        $thisDate = date('Y-m-01');
        return getMonthDiffByDateStr($thisDate,$date);
    }

    /**
     * 数据导出
     * @param $query
     */
    public static function exeDownload($query){
        $head1 = ['品类','平台','SKU','季节性','来源','销售比例','推荐备货比例(7天)','推荐备货比例(30天)','产品名称',
            '3天AVG','7天AVG','15天AVG','30天AVG','AVG',
            'FBA在库','FBA在途', '公共在库','国内在库','国内在途', '海外仓在库','海外仓在途',
            '生产周期','空运时效','空运最快可售', '海运时效','海运最快可售',
            '空运发货安全系数','海运发货安全系数','发货目前系数','空运备货安全系数','海运备货安全系数','备货目前系数'];
        $head2 = [];
        for($i = 0; $i < JpSkuEstimate::ESTIMATE_NUM; $i++){
            $head2[] = date('y/m',strtotime("midnight first day of $i month")).'预估';
        }
        $head3 = ['备货推荐','推荐说明','可调剂库存','积压库存','积压货值','状态','备货员'];
        $head = array_merge($head1,$head2,$head3);
        RExcel::exportCsv('备货产品',function () use ($head, $query) {
            $fp = fopen('php://output', 'w');
            fwrite($fp, chr(0xEF).chr(0xBB).chr(0xBF));
            fputcsv($fp, $head);
            $query->chunk(1000, function ($re) use ($fp){
                $data = self::_getDownloadData($re);
                foreach ($data as $item) {
                    fputcsv($fp, $item);
                }
            });
        });
    }

    private static function _getDownloadData($res)
    {
        $seller = [];
        foreach ($res as $model){
            $sellerId = $model->seller_id ?: 0;
            if(!array_key_exists($sellerId,$seller)){
                $seller[$sellerId] = User::getUsernameById($sellerId);
            }
            $data1 = [
                $model->skuClassify->title ?? '',
                $model->plat->title ?? '',
                $model->sku->psku_code ?? '',
                ProductClassify::$_season[$model->skuClassify->season ?? 3],
                isset($model->area_sku->type) && $model->area_sku->type ? JpSku::$_type[$model->area_sku->type] : '',
                (isset($model->ratio->ratio) && $model->ratio->ratio > 0) ? ($model->ratio->ratio * 100) . '%' : '0%',
                (isset($model->recommend_ratio7) && $model->recommend_ratio7 > 0) ? ($model->recommend_ratio7 * 100) . '%' : '0%',
                (isset($model->recommend_ratio30) && $model->recommend_ratio30 > 0) ? ($model->recommend_ratio30 * 100) . '%' : '0%',
                $model->sku->psku_cn ?? '',
                $model->avg3 ?: 0,
                $model->avg7 ?: 0,
                $model->avg15 ?: 0,
                $model->avg30 ?: 0,
                $model->avg ?: 0,
                $model->fba_instant_num ?: 0,
                $model->fba_transit_num ?: 0,
                PublicCn::getInstant($model->psku_id),
                $model->cn_instant_num ?: 0,
                $model->cn_transit_num ?: 0,
                $model->os_instant_num ?: 0,
                $model->os_transit_num ?: 0,
                $model->purchase_cycle ?: 0,
                $model->air_day ?: 0,
                self::getSaleDate($model->air_available),
                $model->sea_day ?: 0,
                self::getSaleDate($model->sea_available),
                $model->air_ship_factor,
                $model->sea_ship_factor,
                self::getShipWithOSCurrentFactor($model).'('.self::getShipCurrentFactor($model).')',
                $model->air_stock_factor,
                $model->sea_stock_factor,
                self::getStockCurrentFactor($model)];
            $data2 = [];
            for($i = 0; $i < JpSkuEstimate::ESTIMATE_NUM; $i++){
                $data2[] = $model->{"estimate_num$i"} ?: 0;
            }
            $tj = self::getSystemTJ($model);
            $desc = [];
            foreach($tj['desc'] as $key => $_desc){
                $desc[] = $key.'-'.$_desc;
            }
            $data3 = [
                $tj['num'],
                implode(';',$desc),
                $tj['free'],
                $model->over_stock,
                $model->over_stock_value,
                self::$_status[$model->status],
                $seller[$sellerId],
            ];
            $temp = array_merge($data1,$data2,$data3);
            yield $temp;
        }
    }
}
